package com.mysql;

import java.sql.*;
import java.util.ArrayList;
import com.dom.*;

public class mysqlConnect {

    // 设置数据库连接的相关信息
    // 数据库地址、用户名、密码
    static final private String DB_URL="jdbc:mysql://localhost:3306/zaiqing?";
    static final private String USER="root";
    static final private String PASS="jn123528";
    private Connection conn;
    private Statement stmt;

    public mysqlConnect(){
        // 通过构造函数来建立连接
        // 在新建对象是建立一个新的连接，使资源做到用时开启，不用关闭
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public ArrayList selectAllUser(String sql){
        // 获取所有用户信息，返回一个用户类数组
        ArrayList res = new ArrayList();
        try{
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                user one = new user();
                one.setName(rs.getString("name"));
                one.setSex(rs.getString("sex"));
                one.setPassword(rs.getString("password"));
                one.setAge(rs.getInt("age"));
                one.setDegree(rs.getString("degree"));
                one.setJob(rs.getString("job"));
                one.setType(rs.getInt("type"));
                res.add(one);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

    public user selectByID(String name){
        // 通过用户名来获取单个用户的信息，返回单个用户类
        user one = new user();
        try{
            String sql = "select * from users where name='" + name + "'";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()){
                one.setName(rs.getString("name"));
                one.setSex(rs.getString("sex"));
                one.setPassword(rs.getString("password"));
                one.setAge(rs.getInt("age"));
                one.setDegree(rs.getString("degree"));
                one.setJob(rs.getString("job"));
                one.setType(rs.getInt("type"));
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return one;
    }

    public boolean insertUser(user temp){
        // 将前端返回的用户信息传入数据库
        // 插入成功返回true，反之返回false
        boolean flag = false;
        String sql = "insert into users" +
                "(name, sex, password, age, degree, job, type) " +
                "values(?, ?, ?, ?, ?, ?, ?)";
        System.out.println(sql);
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, temp.getName());
            ps.setString(2, temp.getSex());
            ps.setString(3, temp.getPassword());
            ps.setInt(4, temp.getAge());
            ps.setString(5, temp.getDegree());
            ps.setString(6, temp.getJob());
            ps.setInt(7, temp.getType());;
            int a = ps.executeUpdate();
            if (a > 0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean updateUser(user temp){
        // 将用户修改后的信息返回到数据库，进行信息的更新
        // 若成功返回true，反之返回false
        boolean flag = false;
        try{
            String sql = "update users set sex='" + temp.getSex() +"',password='"
                    + temp.getPassword() + "',age='" + temp.getAge() +
                    "',degree='" + temp.getDegree() + "',job='" +
                    temp.getJob() + "' where name='" + temp.getName() + "'";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a > 0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteUser(String name){
        // 根据用户名删除用户信息
        // 成功返回true，失败返回false
        boolean flag = false;
        try{
            String sql = "delete from users where name='" + name + "';";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a>0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

    public ArrayList selectAllinfo(String sql){
        // 获取所有已经通过的灾害事件信息，返回事件类数组
        ArrayList res = new ArrayList();
        try{
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                info one = new info();
                one.setId(rs.getInt("id"));
                one.setType(rs.getString("type"));
                one.setLocation(rs.getString("location"));
                one.setLon(rs.getFloat("lon"));
                one.setLat(rs.getFloat("lat"));
                one.setName(rs.getString("name"));
                one.setDate(rs.getDate("time"));
                one.setDegree(rs.getString("degree"));
                one.setReaction(rs.getString("reaction"));
                one.setDestory(rs.getFloat("destory"));
                one.setDead(rs.getInt("dead"));
                one.setHurt(rs.getInt("hurt"));
                one.setAid(rs.getString("aid"));
                one.setElec(rs.getString("elec"));
                one.setConcat(rs.getString("concat"));
                one.setTraffic(rs.getString("traffic"));
                one.setWater(rs.getString("water"));
                one.setGas(rs.getString("gas"));
                res.add(one);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

    public ArrayList selectAlltempinfo(String sql){
        // 获取所有待审核的灾害事件信息，返回事件类数组
        ArrayList res = new ArrayList();
        try{
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                info one = new info();
                one.setId(rs.getInt("id"));
                one.setType(rs.getString("type"));
                one.setLocation(rs.getString("location"));
                one.setLon(rs.getFloat("lon"));
                one.setLat(rs.getFloat("lat"));
                one.setName(rs.getString("name"));
                one.setDate(rs.getDate("time"));
                one.setDegree(rs.getString("degree"));
                one.setReaction(rs.getString("reaction"));
                one.setDestory(rs.getFloat("destory"));
                one.setDead(rs.getInt("dead"));
                one.setHurt(rs.getInt("hurt"));
                one.setAid(rs.getString("aid"));
                one.setElec(rs.getString("elec"));
                one.setConcat(rs.getString("concat"));
                one.setTraffic(rs.getString("traffic"));
                one.setWater(rs.getString("water"));
                one.setGas(rs.getString("gas"));
                res.add(one);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

    public ArrayList selectInfoByName(String name){
        // 通过申报人用户名获取此用户已通过的所有事件信息
        // 返回事件类数组
        ArrayList res = new ArrayList();
        try{
            String sql = "select * from infos where name='" + name + "'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                info one = new info();
                one.setId(rs.getInt("id"));
                one.setType(rs.getString("type"));
                one.setLocation(rs.getString("location"));
                one.setLon(rs.getFloat("lon"));
                one.setLat(rs.getFloat("lat"));
                one.setName(rs.getString("name"));
                one.setDate(rs.getDate("time"));
                res.add(one);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

    public ArrayList selectTempInfoByName(String name){
        // 通过申报人用户名获取此用户未通过的所有事件信息
        // 返回事件类数组
        ArrayList res = new ArrayList();
        try{
            String sql = "select * from tempinfos where name='" + name + "'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                info one = new info();
                one.setId(rs.getInt("id"));
                one.setType(rs.getString("type"));
                one.setLocation(rs.getString("location"));
                one.setLon(rs.getFloat("lon"));
                one.setLat(rs.getFloat("lat"));
                one.setName(rs.getString("name"));
                one.setDate(rs.getDate("time"));
                res.add(one);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

    public boolean insertInfo(info temp){
        // 将通过审核的事件信息插入到数据库之中
        // 若成功即返回true, 失败返回false
        boolean flag = false;
        String sql = "insert into infos"  +
                "(id, type, location, lon, lat, name, time, degree, reaction, destory, dead, hurt, aid, elec, concat, traffic, water, gas) " +
                "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        System.out.println(sql);
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, temp.getId());
            ps.setString(2, temp.getType());
            ps.setString(3, temp.getLocation());
            ps.setFloat(4, temp.getLon());
            ps.setFloat(5, temp.getLat());
            ps.setString(6, temp.getName());
            ps.setDate(7, new java.sql.Date(temp.getDate().getTime()));
            ps.setString(8, temp.getDegree());
            ps.setString(9, temp.getReaction());
            ps.setFloat(10, temp.getDestory());
            ps.setInt(11, temp.getDead());
            ps.setInt(12, temp.getHurt());
            ps.setString(13, temp.getAid());
            ps.setString(14, temp.getElec());
            ps.setString(15, temp.getConcat());
            ps.setString(16, temp.getTraffic());
            ps.setString(17, temp.getWater());
            ps.setString(18, temp.getGas());
            int a = ps.executeUpdate();
            if (a > 0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteInfo(String id){
        // 通过灾害事件的ID，从数据删除当前已通过的事件信息
        // 若成功返回true，失败返回false
        boolean flag = false;
        try{
            String sql = "delete from infos" + " where id=" + id + ";";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a>0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteInfoByName(String name){
        // 通过灾害事件的申报人姓名，从数据删除当前申报人已通过的所有事件信息
        // 若成功返回true，失败返回false
        boolean flag = false;
        try{
            String sql = "delete from infos" + " where id=" + name + ";";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a>0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean insertTempInfo(info temp){
        // 将用户提交的事件信息插入到数据库之中
        // 若成功即返回true, 失败返回fals
        boolean flag = false;
        String sql = "insert into tempinfos" +
                "(id, type, location, lon, lat, name, time, degree, reaction, destory, dead, hurt, aid, elec, concat, traffic, water, gas) " +
                "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        System.out.println(sql);
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, temp.getId());
            ps.setString(2, temp.getType());
            ps.setString(3, temp.getLocation());
            ps.setFloat(4, temp.getLon());
            ps.setFloat(5, temp.getLat());
            ps.setString(6, temp.getName());
            ps.setDate(7, new java.sql.Date(temp.getDate().getTime()));
            ps.setString(8, temp.getDegree());
            ps.setString(9, temp.getReaction());
            ps.setFloat(10, temp.getDestory());
            ps.setInt(11, temp.getDead());
            ps.setInt(12, temp.getHurt());
            ps.setString(13, temp.getAid());
            ps.setString(14, temp.getElec());
            ps.setString(15, temp.getConcat());
            ps.setString(16, temp.getTraffic());
            ps.setString(17, temp.getWater());
            ps.setString(18, temp.getGas());
            int a = ps.executeUpdate();
            if (a > 0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteTempInfo(String id){
        // 通过灾害事件的ID，从数据删除当前已通过的事件信息
        // 若成功返回true，失败返回false
        boolean flag = false;
        try{
            String sql = "delete from tempinfos" + " where id=" + id + ";";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a>0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

    public boolean deleteTempInfoByName(String name){
        // 通过灾害事件的申报人姓名，从数据删除当前申报人未通过的所有事件信息
        // 若成功返回true，失败返回false
        boolean flag = false;
        try{
            String sql = "delete from tempinfos" + " where id=" + name + ";";
            System.out.println(sql);
            PreparedStatement ps = conn.prepareStatement(sql);
            int a = ps.executeUpdate();
            if (a>0) flag = true;
            ps.close();
            stmt.close();
            conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

}
